/**
 * @author lyf
 * @email jjxliu306@163.com
 * @date 2018年10月31日 下午11:51:38
 * 
 */
package com.ng.stat.controller;

import java.net.URLDecoder;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ng.common.utils.R;
import com.ng.common.utils.Excel.ExcelUtils;
import com.ng.common.utils.Excel.HlSheet;
import com.ng.stat.service.StatSqlService;

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;

/** 
 *  统计入口
 * @author lyf
 * @email jjxliu306@163.com
 * @date 2017年10月31日 下午11:51:38
 */
@RestController
@RequestMapping("stat")
@Api(value="基于数据库sql的查询接口" , tags= {"基于数据库sql的查询接口-通用"})
public class StatController {

	Logger logger = LoggerFactory.getLogger(getClass());
	
	@Autowired
	private StatSqlService sqlService ;
	
	//@SysLog("基于sql查询数据")
	@RequestMapping(value="/selectSql/{id}" , method= {RequestMethod.GET,RequestMethod.POST})
	@ApiOperation("基于sql查询数据")
	public R<Object> selectSql(@ApiParam(name="id",value="定制sqlID" , required=true) @PathVariable("id") String id ,
			@ApiParam(name="params",value="查询参数,可为空" , required=false) @RequestBody   Object[] params) {
		
		logger.info("select sql ,id:" + id + " , params : " + JSON.toJSONString(params));
		
		return sqlService.select(id, params);
	}
	
	//@SysLog("基于数据库函数查询数据") 
	@RequestMapping(value="/selectFunction/{function}" , method= {RequestMethod.GET,RequestMethod.POST})
	@ApiOperation("基于数据库函数查询数据")
	public R<Object> selectFunction(@ApiParam(name="function",value="函数名称" , required=true) @PathVariable("function") String function ,
			@ApiParam(name="params",value="查询参数,可为空" , required=false) @RequestBody   Object[] params) {
		
		logger.info("select function sql ,function:" + function + " , params : " + JSON.toJSONString(params));
		
		return sqlService.selectByFunction(function, params);
	}
	 
	//@SysLog("基于数据库函数导出数据excel数据")
	@GetMapping(value="/exportByFunction/{function}" ) //exportFunction/report_zhongce_defineData
	@ApiOperation("基于数据库函数导出数据")
	public void exportByFunction(@ApiParam(name="function",value="函数名称" , required=true) @PathVariable String function ,
			@ApiParam(name="params",value="查询参数,可为空" , required=false)  String params,
			@ApiParam(name="filename",value="文件名称" , required=false)  String filename,
			HttpServletResponse response) {
		
		logger.info("export function sql ,function: report_zhongce_defineData  , params : " + JSON.toJSONString(params));
		
		
		// 约定 这种导出函数返回的结构为json 包含columns-excel表头文字 , list 结构内容 填充到具体行,分号分隔字符串
		

		@SuppressWarnings("deprecation")
		String decode = URLDecoder.decode(params);
		
		String[] ps = {decode};
		
		// 查询数据
		String data = sqlService.selectFunction(function,ps);
		
		JSONObject jo = JSON.parseObject(data);
		
		List<HlSheet> sheets = new ArrayList<>();
		// 循环判断
		for(int i = 1 ; i < 10 ; i++) {
			
			if(!jo.containsKey("column" + i)) {
				break ;
			}
			
			JSONArray array = jo.getJSONArray("list" + i);
			String columns = jo.getString("column" + i);
			String sheetName = jo.getString("sheet" + i);
			
			// 写入表头
		  	 
			
			// 数据 
			Iterator<Object> iters =	array.iterator();
		 
			HlSheet sheet = new HlSheet(sheetName, columns.split(";"));
			
			
			while(iters.hasNext()) {
				Object iter = iters.next();
				
				if(iter != null) {
					String[] ss = iter.toString().split(";");
					 
					 
					sheet.addLine(ss);
				}
				
			}
			
			sheets.add(sheet);
			
		}
		
		
		 
		HlSheet[] ss = sheets.toArray(new HlSheet[0]);
		
		ExcelUtils.writeExcel(response, filename, ss);
		
		
		
	}
	 
	
	
}
